package com.link2room.ironhide.service.impl;

import com.link2room.common.dao.ICommonDao;
import com.link2room.common.jpa.result.QueryResult;
import com.link2room.common.util.MapUtil;
import com.link2room.common.util.StringUtil;
import com.link2room.ironhide.dto.param.api.HotelsApiParamDto;
import com.link2room.ironhide.mapper.HotelInfoMapper;
import com.link2room.ironhide.service.IHotelApiService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by sss on 2016/9/19.
 */
@Service("hotelApiService")
public class HotelApiServiceImpl implements IHotelApiService {

    @Resource
    private ICommonDao commonDao;

    @Override
    public QueryResult<HotelInfoMapper> listHotelInfo(HotelsApiParamDto hotelsApiParamDto)throws Exception{
        List objects=new ArrayList<>();
        List countbjects=new ArrayList<>();

        String querySQL = this.listHotelInfoSQL(hotelsApiParamDto,objects);
        String countSQL=this.countHotelInfoSQL(hotelsApiParamDto,countbjects);
        QueryResult<HotelInfoMapper> hotelInfoMapperResult=commonDao.findByNative(querySQL,countSQL,objects,countbjects, HotelInfoMapper.class,hotelsApiParamDto.getCurrentPage(),hotelsApiParamDto.getPageSize());
        return hotelInfoMapperResult;
    }

    private String listHotelInfoSQL(HotelsApiParamDto hotelsApiParamDto, List objects)throws Exception{
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT " +
                "  hb.lhotel_group_code lhotelGroupCode," +
                "  hb.lhotel_code lhotelCode," +
                "  hb.name name," +
                "  hb.name_en nameEn," +
                "  hb.used_name usedName," +
                "  hb.status status," +
                "  hb.country country," +
                "  hb.province province," +
                "  hb.city city," +
                "  hb.district district," +
                "  hb.address address," +
                "  hb.address_en addressEn," +
                "  hb.longitude longitude," +
                "  hb.latitude latitude," +
                "  hb.position_type positionType," +
                "  hb.tel_reception telReception," +
                "  hb.tel_booking telBooking," +
                "  hb.tel_400 tel400," +
                "  hb.brand brand," +
                "  hb.room_num roomNum," +
                "  hb.descript descript," +
                "  hb.descript_en descriptEn," +
                "  hb.descript_m descriptM," +
                "  hb.descript_m_en descriptMEn," +
                "  hb.book_list_order bookListOrder," +
                "  hb.list_order listOrder," +
                "  hb.hotel_tag hotelTag," +
                "  hb.search_code searchCode," );
        if(hotelsApiParamDto.getLongitude()!=null&&hotelsApiParamDto.getLatitude()!=null){
            sb.append("CONVERT(2 * ASIN(SQRT(POW(SIN((hb.`latitude`*3.14159265358979323846/180.0-"+hotelsApiParamDto.getLatitude()+"*3.14159265358979323846/180.0) / 2), 2)\n" +
                    "                + COS(hb.`latitude`*3.14159265358979323846/180.0) * COS("+hotelsApiParamDto.getLatitude()+"*3.14159265358979323846/180.0)\n" +
                    "                * POW(SIN((hb.`longitude`*3.14159265358979323846/180.0-"+hotelsApiParamDto.getLongitude()+"*3.14159265358979323846/180.0) / 2), 2)))*6378.137,DECIMAL(12,2)) distance,");
        }
        sb.append("  IFNULL((select hm.min_price from hotel_min_price hm where hm.lhotel_group_code = hb.lhotel_group_code " +
                "    AND hm.lhotel_code = hb.lhotel_code AND hm.pb_channel=?1 AND hm.rsv_date = ?2 ),9999) hotelMinPrice" +
                " FROM" +
                "  l2r_hotel_baseinfo hb " );
        boolean positionFlag=false;
        if(hotelsApiParamDto.getPositions()!=null&&hotelsApiParamDto.getPositions().size()>0) {
            sb.append(" INNER JOIN l2r_hotel_position hp ON hb.lhotel_group_code=hp.lhotel_group_code and hb.lhotel_code=hp.lhotel_code ");
            positionFlag=true;
        }
        sb.append("WHERE hb.lhotel_group_code = ?3  ");
        objects.add(hotelsApiParamDto.getPbChannel());
        objects.add(hotelsApiParamDto.getCheckInDatetime());
        objects.add(hotelsApiParamDto.getLhotelGroupCode());
        int num=3;
        if(StringUtil.isNotBlank(hotelsApiParamDto.getLhotelCode())){
            num++;
            sb.append("  AND hb.lhotel_code = ?" +num+" ");
            objects.add(hotelsApiParamDto.getLhotelCode());
        }
        if(hotelsApiParamDto.getProvince()!=null){
            num++;
            sb.append("  AND hb.province = ?" +num+" ");
            objects.add(hotelsApiParamDto.getProvince());
        }
        if(hotelsApiParamDto.getCity()!=null){
            num++;
            sb.append("  AND hb.city = ?" +num+" ");
            objects.add(hotelsApiParamDto.getCity());
        }
        if(hotelsApiParamDto.getLongitude()!=null&&hotelsApiParamDto.getLatitude()!=null){
            double[] around = MapUtil.getAround(hotelsApiParamDto.getLongitude(), hotelsApiParamDto.getLatitude(), hotelsApiParamDto.getRaidus());
            num++;
            sb.append("  AND hb.latitude >= ?" +num+"  " );
            num++;
            sb.append("  AND hb.longitude >= ?" +num+" ");
            num++;
            sb.append("  AND hb.latitude <= ?" +num+"  " );
            num++;
            sb.append("  AND hb.longitude <= ?" +num+" " );
            objects.add(around[0]);
            objects.add(around[1]);
            objects.add(around[2]);
            objects.add(around[3]);
        }
        if(hotelsApiParamDto.getPositions()!=null&&hotelsApiParamDto.getPositions().size()>0){
            sb.append("  AND hp.position_code IN (");
            for (String position:hotelsApiParamDto.getPositions()){
                num++;
                sb.append("?" +num+",");
                objects.add(position);
            }
            sb.deleteCharAt(sb.length()-1);
            sb.append(")");
        }
        if(hotelsApiParamDto.getBrands()!=null&&hotelsApiParamDto.getBrands().size()>0){
            sb.append("  AND hb.brand IN (");
            for (String brand:hotelsApiParamDto.getBrands()){
                num++;
                sb.append("?" +num+",");
                objects.add(brand);
            }
            sb.deleteCharAt(sb.length()-1);
            sb.append(")");
        }
        if(StringUtil.isNotBlank(hotelsApiParamDto.getKey())){
            num++;
            String key="%"+hotelsApiParamDto.getKey()+"%";
            sb.append("  AND (hb.name like ?" +num+" " );
            num++;
            sb.append("  OR hb.name_en like ?" +num+" " );
            num++;
            sb.append("  OR hb.address like ?" +num+" " );
            num++;
            sb.append("  OR hb.address_en like ?" +num+" )" );
            objects.add(key);
            objects.add(key);
            objects.add(key);
            objects.add(key);
        }

        sb.append("  AND hb.is_halt='F' ");
        if(positionFlag){
            sb.append("  AND hp.is_halt='F' ");
        }

        if(StringUtil.isBlank(hotelsApiParamDto.getSortBy())){
            sb.append( " ORDER BY listOrder,hb.id " );
        } else {
            if("distance".equals(hotelsApiParamDto.getSortBy())){
                sb.append( " ORDER BY "+hotelsApiParamDto.getSortBy()+"*1 " );
            }else {
                sb.append( " ORDER BY "+hotelsApiParamDto.getSortBy()+" " );
            }
        }
        if(StringUtil.isNotBlank(hotelsApiParamDto.getOrder())){
            sb.append(hotelsApiParamDto.getOrder());
        }else {
            sb.append(" ASC");
        }
        return sb.toString();
    }

    private String countHotelInfoSQL(HotelsApiParamDto hotelsApiParamDto, List countObjects)throws Exception{
        StringBuffer sb=new StringBuffer();
        int num=1;
        sb.append("SELECT count(*)" +
                " FROM" +
                "  l2r_hotel_baseinfo hb ");
        boolean positionFlag=false;
        if(hotelsApiParamDto.getPositions()!=null&&hotelsApiParamDto.getPositions().size()>0) {
            sb.append(" INNER JOIN l2r_hotel_position hp ON hb.lhotel_group_code=hp.lhotel_group_code and hb.lhotel_code=hp.lhotel_code ");
            positionFlag=true;
        }
        sb.append("WHERE hb.lhotel_group_code = ?" +num+" ");
        countObjects.add(hotelsApiParamDto.getLhotelGroupCode());
        if(StringUtil.isNotBlank(hotelsApiParamDto.getLhotelCode())){
            num++;
            sb.append("  AND hb.lhotel_code = ?" +num+" ");
            countObjects.add(hotelsApiParamDto.getLhotelCode());
        }
        if(hotelsApiParamDto.getProvince()!=null){
            num++;
            sb.append("  AND hb.province = ?" +num+" ");
            countObjects.add(hotelsApiParamDto.getProvince());
        }
        if(hotelsApiParamDto.getCity()!=null){
            num++;
            sb.append("  AND hb.city = ?" +num+" ");
            countObjects.add(hotelsApiParamDto.getCity());
        }
        sb.append("  AND hb.is_halt='F' ");

        if(hotelsApiParamDto.getLongitude()!=null&&hotelsApiParamDto.getLatitude()!=null){
            double[] around = MapUtil.getAround(hotelsApiParamDto.getLongitude(), hotelsApiParamDto.getLatitude(), hotelsApiParamDto.getRaidus());
            num++;
            sb.append("  AND hb.latitude >= ?" +num+"  " );
            num++;
            sb.append("  AND hb.longitude >= ?" +num+" ");
            num++;
            sb.append("  AND hb.latitude <= ?" +num+"  " );
            num++;
            sb.append("  AND hb.longitude <= ?" +num+" " );
            countObjects.add(around[0]);
            countObjects.add(around[1]);
            countObjects.add(around[2]);
            countObjects.add(around[3]);
        }
        if(hotelsApiParamDto.getPositions()!=null&&hotelsApiParamDto.getPositions().size()>0){
            sb.append("  AND hp.position_code IN (");
            for (String position:hotelsApiParamDto.getPositions()){
                num++;
                sb.append("?" +num+",");
                countObjects.add(position);
            }
            sb.deleteCharAt(sb.length()-1);
            sb.append(")");
        }
        if(hotelsApiParamDto.getBrands()!=null&&hotelsApiParamDto.getBrands().size()>0){
            sb.append("  AND hb.brand IN (");
            for (String brand:hotelsApiParamDto.getBrands()){
                num++;
                sb.append("?" +num+",");
                countObjects.add(brand);
            }
            sb.deleteCharAt(sb.length()-1);
            sb.append(")");
        }
        if(StringUtil.isNotBlank(hotelsApiParamDto.getKey())){
            num++;
            String key="%"+hotelsApiParamDto.getKey()+"%";
            sb.append("  AND (hb.name like ?" +num+" " );
            num++;
            sb.append("  OR hb.name_en like ?" +num+" " );
            num++;
            sb.append("  OR hb.address like ?" +num+" " );
            num++;
            sb.append("  OR hb.address_en like ?" +num+" )" );
            countObjects.add(key);
            countObjects.add(key);
            countObjects.add(key);
            countObjects.add(key);
        }
        sb.append("  AND hb.is_halt='F' ");
        if(positionFlag){
            sb.append("  AND hp.is_halt='F' ");
        }
        return sb.toString();
    }
}
